﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

using XianShang.Data;
using XianShang.Entity;
using XianShang.Common;

namespace XianShang.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
        /// <summary>
        /// 獲取留言信息的總數
        /// </summary>
        /// <returns></returns>
        public int GetMsgNum()
        {
            string commandText = "SELECT Count(id) FROM [dbo].[XS_LeaveMsg] WHERE [show]<>0 ";

            return Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text, commandText), -1);
        }

        /// <summary>
        /// 獲取信息顯示留言信息列表條件 show=true
        /// </summary>
        /// <returns></returns>
        public IDataReader GetMsgInfo()
        {
            string commandText = "SELECT * FROM [dbo].[XS_LeaveMsg] WHERE [show]<>0 order by [Time] desc";

            return DbHelper.ExecuteReader(CommandType.Text, commandText);
        }

        /// <summary>
        /// 獲取信息顯示留言信息列表
        /// </summary>
        /// <returns></returns>
        public IDataReader GetAllMsgInfo()
        {
            string commandText = "SELECT * FROM [dbo].[XS_LeaveMsg] order by [Time] desc";

            return DbHelper.ExecuteReader(CommandType.Text, commandText);
        }
        /// <summary>
        /// 根據更新一條留言信息
        /// </summary>
        /// <returns></returns>
        public int UpMsgInfoById(LeaveMsgInfo msg)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@Content",(DbType)SqlDbType.NText,1073741823,msg.Content),
                                       DbHelper.MakeInParam("@ReContent",(DbType)SqlDbType.NText,1073741823,msg.ReContent),
                                       DbHelper.MakeInParam("@Title",(DbType)SqlDbType.NVarChar,200,msg.Title),
                                       DbHelper.MakeInParam("@Show",(DbType)SqlDbType.Bit,4,msg.Show),
                                       DbHelper.MakeInParam("@ID",(DbType)SqlDbType.Int,int.MinValue,msg.ID)
                                   };
            string commandText = "UPDATE [dbo].[XS_LeaveMsg] SET [Title]=@title,[Content]=@Content,[ReContent]=@ReContent,[Show]=@Show where [ID]=@ID";

            return Utils.StrToInt(DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar), -1);
        }

        /// <summary>
        ///根據留言ID刪除留言信息
        /// </summary>
        /// <returns></returns>
        public int DelMsgInfo(string Id)
        {
            DbParameter getPar = DbHelper.MakeInParam("@Id", (DbType)SqlDbType.Int, int.MaxValue, Id);

            string commandText = "DELETE FROM [dbo].[XS_LeaveMsg] WHERE [ID]=@Id";

            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 插入一條新紀錄
        /// </summary>
        /// <param name="msg"></param>
        /// <returns></returns>
        public int AddMsgInfo(LeaveMsgInfo msg)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@Name",(DbType)SqlDbType.NVarChar,100,msg.Name),
                                       DbHelper.MakeInParam("@Content",(DbType)SqlDbType.NText,1073741823,msg.Content),
                                       DbHelper.MakeInParam("@Show",(DbType)SqlDbType.Bit,4,msg.Show),
                                       DbHelper.MakeInParam("@Title",(DbType)SqlDbType.NVarChar,200,msg.Title),
                                       DbHelper.MakeInParam("@Time",(DbType)SqlDbType.DateTime,50,msg.Time),
                                       DbHelper.MakeInParam("@Ip",(DbType)SqlDbType.NVarChar,50,msg.IP)
                                   };
            string commandText = "INSERT INTO [dbo].[XS_LeaveMsg]([Name],[Title],[Content],[IP],[Show],[Time])";
            commandText += "VALUES(@name,@title,@content,@ip,@show,@time)";

            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據ID取得留言信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetMsgInfoById(string id)
        {
            DbParameter getPar = DbHelper.MakeInParam("@Id", (DbType)SqlDbType.Int, int.MaxValue, id);

            string commandText = "Select * FROM [dbo].[XS_LeaveMsg] WHERE [ID]=@Id";

            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar).Tables[0];
        }
        /// <summary>
        /// 根據ID修改 recontent retime show 
        /// </summary>
        /// <param name="msg"></param>
        /// <returns></returns>
        public int UpDateReContentAndReTimeAndShowById(LeaveMsgInfo msg)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, msg.ID),
                                       DbHelper.MakeInParam("@show",(DbType)SqlDbType.Bit,4,msg.Show),
                                       DbHelper.MakeInParam("@retime",(DbType)SqlDbType.DateTime,50,msg.ReTime),
                                       DbHelper.MakeInParam("@recontent",(DbType)SqlDbType.NVarChar,500,msg.ReContent),
                                       DbHelper.MakeInParam("@time",(DbType)SqlDbType.DateTime,50,msg.Time)
                                   };

            string commandText = "UPDATE [dbo].[XS_LeaveMsg] SET [ReContent]=@recontent,[Show]=@show,[ReTime]=@retime WHERE [ID]=@id";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據id修改Show
        /// </summary>
        /// <param name="msg"></param>
        /// <returns></returns>
        public int UpDateShowById(LeaveMsgInfo msg)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, msg.ID),
                                       DbHelper.MakeInParam("@show",(DbType)SqlDbType.Bit,4,msg.Show)
                                   };

            string commandText = "UPDATE [dbo].[XS_LeaveMsg] SET [Show]=@show WHERE [ID]=@id";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
    }
}
